﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Proy200Millas_BE;

namespace Proy200Millas_ADO
{
    public class FaenaADO
    {
        ConexionADO MiConexion = new ConexionADO();
        SqlConnection cnx = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        SqlDataReader dtr = default(SqlDataReader);
        Boolean vexito = false;

        public DataTable ListarFaena()
        {
            DataSet dts = new DataSet();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_Listar_Faena";
                SqlDataAdapter miada = default(SqlDataAdapter);
                miada = new SqlDataAdapter(cmd);
                miada.Fill(dts, "Faena");

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return dts.Tables["Faena"];
        }

        public bool InsertarFaena(FaenaBE _faenaBE)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_InsertarFaena";
            //Agregamos parametros 
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vfec_ini", SqlDbType.SmallDateTime));
                cmd.Parameters["@vfec_ini"].Value = _faenaBE.Fec_ini;
                cmd.Parameters.Add(new SqlParameter("@vfec_fin", SqlDbType.SmallDateTime));
                cmd.Parameters["@vfec_fin"].Value = _faenaBE.Fec_fin;
                cmd.Parameters.Add(new SqlParameter("@vzon_mar", SqlDbType.VarChar, 1));
                cmd.Parameters["@vzon_mar"].Value = _faenaBE.Zon_mar;
                cmd.Parameters.Add(new SqlParameter("@vcod_bar", SqlDbType.VarChar, 3));
                cmd.Parameters["@vcod_bar"].Value = _faenaBE.Cod_bar;
                cmd.Parameters.Add(new SqlParameter("@vest_fae", SqlDbType.VarChar, 1));
                cmd.Parameters["@vest_fae"].Value = _faenaBE.Est_fae;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;
            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                // No olvidarse!!!!!!
                cmd.Parameters.Clear();
            }
            return vexito;
        }

        public bool ActualizarFaena(FaenaBE _faenaBE)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_ActualizarFaena";
            //Agregamos parametros 
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vnum_fae", SqlDbType.Char, 3));
                cmd.Parameters["@vnum_fae"].Value = _faenaBE.Num_fae;
                cmd.Parameters.Add(new SqlParameter("@vfec_ini", SqlDbType.SmallDateTime));
                cmd.Parameters["@vfec_ini"].Value = _faenaBE.Fec_ini;
                cmd.Parameters.Add(new SqlParameter("@vfec_fin", SqlDbType.SmallDateTime));
                cmd.Parameters["@vfec_fin"].Value = _faenaBE.Fec_fin;
                cmd.Parameters.Add(new SqlParameter("@vzon_mar", SqlDbType.VarChar, 1));
                cmd.Parameters["@vzon_mar"].Value = _faenaBE.Zon_mar;
                cmd.Parameters.Add(new SqlParameter("@vcod_bar", SqlDbType.VarChar, 3));
                cmd.Parameters["@vcod_bar"].Value = _faenaBE.Cod_bar;
                cmd.Parameters.Add(new SqlParameter("@vest_fae", SqlDbType.VarChar, 1));
                cmd.Parameters["@vest_fae"].Value = _faenaBE.Est_fae;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;
            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                // No olvidarse!!!!!!
                cmd.Parameters.Clear();
            }
            return vexito;
        }

        public bool EliminarFaena(String vnum_fae)
        {
            cnx.ConnectionString = MiConexion.GetCnx();
            cmd.Connection = cnx;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_EliminarFaena";
            //Agregamos parametros 
            try
            {
                cmd.Parameters.Add(new SqlParameter("@vnum_fae", SqlDbType.Char, 3));
                cmd.Parameters["@vnum_fae"].Value = vnum_fae;
                cnx.Open();
                cmd.ExecuteNonQuery();
                vexito = true;
            }
            catch (SqlException x)
            {
                vexito = false;
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                // No olvidarse!!!!!!
                cmd.Parameters.Clear();
            }
            return vexito;

        }


        public FaenaBE ConsultarFaena(string vnum_fae)
        {
            FaenaBE _faenaBE = new FaenaBE();
            try
            {
                cnx.ConnectionString = MiConexion.GetCnx();
                cmd.Connection = cnx;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_Consultar_Faena";
                //Agregamos el parametro para el SP
                cmd.Parameters.Add(new SqlParameter("@vnum_fae", SqlDbType.Char, 3));
                cmd.Parameters["@vnum_fae"].Value = vnum_fae;
                cnx.Open();
                dtr = cmd.ExecuteReader();
                if (dtr.HasRows == true)
                {
                    dtr.Read();
                    var _mifaena = _faenaBE;
                    _mifaena.Num_fae = dtr.GetValue(dtr.GetOrdinal("num_fae")).ToString();
                    _mifaena.Fec_ini = Convert.ToDateTime(dtr.GetValue(dtr.GetOrdinal("fec_ini")));
                    _mifaena.Fec_fin = Convert.ToDateTime(dtr.GetValue(dtr.GetOrdinal("fec_fin")));
                    _mifaena.Zon_mar = dtr.GetValue(dtr.GetOrdinal("zon_mar")).ToString();
                    _mifaena.Cod_bar = dtr.GetValue(dtr.GetOrdinal("cod_bar")).ToString();
                    _mifaena.Est_fae = dtr.GetValue(dtr.GetOrdinal("est_fae")).ToString();
                    dtr.Close();
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cnx.State == ConnectionState.Open)
                {
                    cnx.Close();
                }
                cmd.Parameters.Clear();
            }
            return _faenaBE;
        }


    }
}
